/*
 * XXX: assumption
 * 1. com.oromwin.repos.data.XXXTable className must be equals physical tableName for reflection
 * 2. com.oromwin.repos.data.XXXTable class memeber variables must be start with UpperCase for reflection
 * 3. need to use escape every sql for windows file separator. so use Query instead.
 */
package com.oromwin.repos.common.helper;

import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;

import com.oromwin.repos.common.Logger;
import com.oromwin.repos.common.Query;
import com.oromwin.repos.common.ReposConfig;
import com.oromwin.repos.common.ReposException;
import com.oromwin.repos.common.Common.Status;
import com.oromwin.repos.data.AbstractReposTable;

public class DBHelper {
    private static Log log = Logger.getLog(DBHelper.class);
    private static Connection conn;
    public static boolean isOracle = false;
    public static boolean isMySQL = true; 
    private static final String CRLF = "\n";
    private static final String prefix = "FILE_";
    
    static {
        try {
            String className = ReposConfig.getInstance().getReposDatabaseDriver();
            isOracle = className.equals("oracle.jdbc.driver.OracleDriver");
            isMySQL = className.equals("org.gjt.mm.mysql.Driver");
            Class.forName(className);
            conn = DBHelper.getConnection();
        } catch (Exception e) {
            Logger.errorWithThrowable(log, e, "");
            throw new RuntimeException(e);
        }
    }
    
    public static Connection getConnection() throws SQLException {
        ReposConfig conf = ReposConfig.getInstance();
        String uri = conf.getReposDatabaseUri();
        String host = conf.getReposDatabaseHost();
        String dbname = conf.getReposDatabaseName();
        int port = conf.getReposDatabasePort();
        String user = conf.getReposDatabaseUser();
        String passwd = conf.getReposDatabasePasswd();
        String curi = String.format(uri, host, port, dbname);
        return DriverManager.getConnection(curi, user, passwd);
    }

    public static int getRecordCount(ResultSet rs) throws SQLException {
        int count = 0;
        try {
            rs.last();
            count = rs.getRow();
            rs.beforeFirst();
        } catch (SQLException e) {
            Logger.errorWithThrowable(log, e, "");
        }
        return count;
    }

    public static ResultSet execute(String sql) throws SQLException {
        return conn.createStatement().executeQuery(sql);
    }

    public static void update(String sql) throws SQLException {
        conn.createStatement().executeUpdate(sql);
    }

    public static boolean createDatabase() {
        String createDatabase = "CREATE DATABASE IF NOT EXISTS `repository` DEFAULT CHARSET=utf8";
        try {
            DBHelper.update(createDatabase);
            return true;
        } catch (SQLException e) {
            ReposException.create(log, e);
            return false;
        }
    }

    public static boolean createAndGrantUser(String user, String password) {
        if (isMySQL) {
            String grantUser = "GRANT ALL PRIVILEGES ON `repository`.* TO '" + user + "'@'localhost' IDENTIFIED BY '" + password + "' WITH GRANT OPTION;";
            if (log.isDebugEnabled()) log.debug(grantUser);
            try {
                DBHelper.update(grantUser);
                return true;
            } catch (SQLException e) {
                ReposException.create(log, e);
                return false;
            }
        } else {
            return false;
        }
    }
    
    public static boolean dropTables() {
        if (isMySQL) {
            try {
                String dropTables = "DROP TABLE IF EXISTS `FileTable`;";
                if (log.isDebugEnabled()) log.debug(dropTables);
                DBHelper.update(dropTables);
                dropTables = "DROP TABLE IF EXISTS `MediaTable`;";
                if (log.isDebugEnabled()) log.debug(dropTables);
                DBHelper.update(dropTables);
                dropTables = "DROP TABLE IF EXISTS `VolumeTable`;";
                if (log.isDebugEnabled()) log.debug(dropTables);
                DBHelper.update(dropTables);
                return true;
            } catch (SQLException e) {
                ReposException.create(log, e);
                return false;
            }
        } else {
            return false;
        }
    }

    public static boolean createVolumeTable(boolean print) {
        if (isMySQL) {
            String createTable = "CREATE TABLE IF NOT EXISTS `VolumeTable` ( " + CRLF +
                    "`VolumeId` VARCHAR(100) NOT NULL, " + CRLF +
                    "`VolumeName` VARCHAR(255) NOT NULL DEFAULT ''," + CRLF +
                    "`VolumeStatus` INT(11) NOT NULL DEFAULT 0, " + CRLF +
                    "`CreateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," + CRLF +
                    "`UpdateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," + CRLF +
                    "PRIMARY KEY (VolumeId)" + CRLF +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8;" + CRLF;
            if (print) {
                log.info(createTable);
                return true;
            }
            if (log.isDebugEnabled()) log.debug(createTable);
            try {
                DBHelper.update(createTable);
                return true;
            } catch (SQLException e) {
                ReposException.create(log, e);
                return false;
            }
        } else {
            return false;
        }
    }

    public static boolean createMediaTable(boolean print) {
        if (isMySQL) {
            String createTable = "CREATE TABLE IF NOT EXISTS `MediaTable` ( " + CRLF +
                    "`VolumeId` VARCHAR(100) NOT NULL, " + CRLF +
                    "`MediaId` VARCHAR(100) NOT NULL, " +  CRLF +
                    "`MediaPath` VARCHAR(255) NOT NULL, " +  CRLF +
                    "`MediaMaxBytes` BIGINT(20) NOT NULL DEFAULT '0'," +  CRLF +
                    "`MediaUsedBytes` BIGINT(20) NOT NULL DEFAULT '0'," +  CRLF +
                    "`MediaFileCount` INT(11) NOT NULL DEFAULT '0', " +  CRLF +
                    "`MediaStatus` INT(11) NOT NULL DEFAULT 0, " +  CRLF +
                    "`CreateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," +  CRLF +
                    "`UpdateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," +  CRLF +
                    "FOREIGN KEY ( `VolumeId` ) REFERENCES `VolumeTable` ( `VolumeId` ) ON UPDATE CASCADE ON DELETE CASCADE, " + CRLF + 
                    "PRIMARY KEY ( `MediaId` )" + CRLF +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8;" + CRLF;
            if (print) {
                log.info(createTable);
                return true;
            }
            if (log.isDebugEnabled()) log.debug(createTable);
            try {
                DBHelper.update(createTable);
                return true;
            } catch (SQLException e) {
                ReposException.create(log, e);
                return false;
            }
        } else {
            return false;
        }
    }

    public static boolean createFileTable(boolean print) {
        if (isMySQL) {
            String createTable = "CREATE TABLE IF NOT EXISTS `FileTable` ( " + CRLF +
                    "`VolumeId` VARCHAR(100) NOT NULL, " + CRLF +
                    "`MediaId` VARCHAR(100) NOT NULL, " +  CRLF +
                    "`FileId` VARCHAR(100) NOT NULL, " +  CRLF +
                    "`FileFormat` VARCHAR(100) NOT NULL DEFAULT '', " + CRLF + 
                    "`FilePath` VARCHAR(255) NOT NULL DEFAULT ''," + CRLF + 
                    "`FileBytes` BIGINT(20) NOT NULL DEFAULT '0'," + CRLF + 
                    "`FileCategory` VARCHAR(100) NULL DEFAULT ''," + CRLF + 
                    "`FileStatus` INT(11) NOT NULL DEFAULT 0, " + CRLF + 
                    "`PdfConvFlag` VARCHAR(10) NOT NULL DEFAULT '0', " +  CRLF +
                    "`CompressionFlag` VARCHAR(10) NOT NULL DEFAULT '0', " + CRLF + 
                    "`CreateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," + CRLF + 
                    "`UpdateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," + CRLF + 
                    "FOREIGN KEY ( `VolumeId` ) REFERENCES `VolumeTable` ( `VolumeId` ) ON UPDATE CASCADE ON DELETE CASCADE, " + CRLF + 
                    "FOREIGN KEY ( `MediaId` ) REFERENCES `MediaTable` ( `MediaId` ) ON UPDATE CASCADE ON DELETE CASCADE, " + CRLF + 
                    "PRIMARY KEY ( `FileId` )" + CRLF +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8;" + CRLF;
            if (print) {
                log.info(createTable);
                return true;
            }
            if (log.isDebugEnabled()) log.debug(createTable);
            try {
                DBHelper.update(createTable);
                return true;
            } catch (SQLException e) {
                ReposException.create(log, e);
                return false;
            }
        } else {
            return false;
        }
    }
    
    /**
     * @param tableClass
     * @return
     */
    private static ArrayList<Field> getUsableFields(Class<? extends AbstractReposTable> tableClass) {
        ArrayList<Field> fields = new ArrayList<Field>();
        for (Field field : tableClass.getDeclaredFields()) {
            if (field.getName().compareTo("A") >= 0 && field.getName().compareTo("Z") <= 0) {
                fields.add(field);
            }
        }
        return fields;
    }
    
    public static void insertTable(AbstractReposTable table) throws ReposException {
        try {
            ArrayList<Field> fields = getUsableFields(table.getClass());
            // generating insert query
            String className = table.getClass().getSimpleName();
            Query query = new Query("INSERT INTO " + prefix + className + " ( ");
            for (Field field : fields) {
                query.append("`" + field.getName() + "`, ");
            }
            query.delete(2);
            query.append(" ) VALUES ( ");
            for (int i = 0; i < fields.size(); i++) {
                query.append("?, ");
            }
            query.delete(2);
            query.append(" );");
            // generating prepared statement 
            PreparedStatement pstmt = conn.prepareStatement(query.toString());
            for (int i = 0, j = 1; i < fields.size(); i++) {
                Field field = fields.get(i);
                String methodName = "get" + field.getName();
                try {
                    Method method = table.getClass().getDeclaredMethod(methodName);
                    if (fields.get(i).getType() == String.class) {
                        pstmt.setString(j++, (String) method.invoke(table));
                    } else if (fields.get(i).getType() == Long.class){
                        pstmt.setLong(j++, (Long) method.invoke(table));
                    } else if (fields.get(i).getType() == Integer.class){
                        pstmt.setInt(j++, (Integer) method.invoke(table));
                    } else if (fields.get(i).getType() == Status.class) {
                        pstmt.setInt(j++, (Integer) method.invoke(table));
                    } else if (fields.get(i).getType() == Timestamp.class) {
                        pstmt.setTimestamp(j++, (Timestamp) method.invoke(table));
                    } else if (fields.get(i).getType() == Boolean.class) {
                        pstmt.setBoolean(j++, (Boolean) method.invoke(table));
                    } else if (fields.get(i).getType() == File.class) {
                        pstmt.setString(j++, method.invoke(table).toString());
                    }
                } catch (IllegalAccessException e) {
                    String message = String.format("Illegal class method definition, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                } catch (NoSuchMethodException e) {
                    String message = String.format("Illegal class method name, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                } catch (InvocationTargetException e) {
                    String message = String.format("Illegal class method target, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                }
            }
            if (log.isDebugEnabled()) log.debug(pstmt.toString());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            throw ReposException.create(log, e);
        }
    }
    
    public static List<AbstractReposTable> selectTable(Class<? extends AbstractReposTable> tableClass, String whereClause) throws ReposException {
        List<AbstractReposTable> reposTables = new ArrayList<AbstractReposTable>();
        ArrayList<Field> fields = getUsableFields(tableClass);
        // generating select query
        String className = tableClass.getSimpleName();
        Query query = new Query("SELECT ");
        for (Field field : fields) {
            query.append("`" + field.getName() + "`, ");
        }
        query.delete(2);
        query.append(" FROM " + prefix + className);
        if (whereClause != null) {
            query.append(" WHERE " + whereClause);
        }
        query.append(";");
        // generating prepared statement 
        try {
            PreparedStatement pstmt = conn.prepareStatement(query.toString());
            if (log.isDebugEnabled()) log.debug(pstmt.toString());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                AbstractReposTable reposTable = tableClass.newInstance();
                for (int i = 0; i < fields.size(); i++) {
                    Field field = fields.get(i);
                    String fieldName = field.getName();
                    String methodName = "set" + fieldName;
                    try {
                        Method method = null;
                        if (fields.get(i).getType() == String.class) {
                            method = tableClass.getMethod(methodName, String.class);
                            String value = rs.getString(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == Long.class){
                            method = tableClass.getMethod(methodName, Long.class);
                            Long value = rs.getLong(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == Integer.class){
                            method = tableClass.getMethod(methodName, Integer.class);
                            Integer value = rs.getInt(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == Status.class) {
                            method = tableClass.getMethod(methodName, Integer.class);
                            Integer value = rs.getInt(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == Timestamp.class) {
                            method = tableClass.getMethod(methodName, Timestamp.class);
                            Timestamp value = rs.getTimestamp(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == Boolean.class) {
                            method = tableClass.getMethod(methodName, Boolean.class);
                            Boolean value = rs.getBoolean(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == File.class) {
                            method = tableClass.getMethod(methodName, File.class);
                            String value = rs.getString(fieldName);
                            method.invoke(reposTable, new File(value));
                        }
                        
                    } catch (IllegalAccessException e) {
                        String message = String.format("Illegal class method definition, class:method {%s:%s}", className, methodName);
                        throw ReposException.create(log, e, message);
                    } catch (NoSuchMethodException e) {
                        String message = String.format("Illegal class method name, class:method {%s:%s}", className, methodName);
                        throw ReposException.create(log, e, message);
                    } catch (InvocationTargetException e) {
                        String message = String.format("Illegal class method target, class:method {%s:%s}", className, methodName);
                        throw ReposException.create(log, e, message);
                    } catch (IllegalArgumentException e) {
                        String message = String.format("Illegal method argument, reposTable:fieldName {%s:%s}", reposTable, fieldName);
                        throw ReposException.create(log, e, message);
                    } catch (SecurityException e) {
                        String message = String.format("Illegal method access, methodName:className {%s:%s}", methodName, className);
                        throw ReposException.create(log, e, message);
                    }
                }
                reposTables.add(reposTable);
            }
            pstmt.close();
        } catch (InstantiationException e) {
            String message = String.format("No default constructor in class, class:stmt {%s:%s}", className, query.toString());
            throw ReposException.create(log, e, message);
        } catch (IllegalAccessException e) {
            String message = String.format("Illegal instance access, class:stmt {%s:%s}", className, query.toString());
            throw ReposException.create(log, e, message);
        } catch (SQLException e) {
            throw ReposException.create(log, e, query.toString());
        }
        return reposTables;
    }
    
    public static void updateTable(AbstractReposTable table, String whereClause) throws ReposException {
        try {
            ArrayList<Field> fields = getUsableFields(table.getClass());
            // generating insert query
            String className = table.getClass().getSimpleName();
            Query query = new Query("UPDATE " + prefix + className + " SET ");
            for (int i = 0; i < fields.size(); i++) {
                Field field = fields.get(i);
                String methodName = "get" + field.getName();
                try {
                    Method method = table.getClass().getDeclaredMethod(methodName);
                    if (fields.get(i).getType() == String.class) {
                        String value = (String) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("`%s` = '%s', ", field.getName(), value));
                        }
                    } else if (fields.get(i).getType() == Long.class){
                        Long value = (Long) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("`%s` = %d, ", field.getName(), value));
                        }
                    } else if (fields.get(i).getType() == Integer.class){
                        Integer value = (Integer) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("`%s` = %d, ", field.getName(), value));
                        }
                    } else if (fields.get(i).getType() == Status.class) {
                        Integer value = (Integer) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("`%s` = '%d', ", field.getName(), value));
                        }
                    } else if (fields.get(i).getType() == Timestamp.class) {
                        Timestamp value = (Timestamp) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("`%s` = '%d', ", field.getName(), value));
                        }
                    } else if (fields.get(i).getType() == Boolean.class) {
                        Boolean value = (Boolean) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("`%s` = '%s', ", field.getName(), value));
                        }
                    } else if (fields.get(i).getType() == File.class) {
                        File value = (File) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("`%s` = '%s', ", field.getName(), value.toString()));
                        }
                    }
                } catch (IllegalAccessException e) {
                    String message = String.format("Illegal class method definition, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                } catch (NoSuchMethodException e) {
                    String message = String.format("Illegal class method name, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                } catch (InvocationTargetException e) {
                    String message = String.format("Illegal class method target, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                }
            }
            query.delete(2);
            if (whereClause != null) {
                query.append(" WHERE " + whereClause);
            }
            query.append(";");
            // generating prepared statement 
            PreparedStatement pstmt = conn.prepareStatement(query.toString());
            if (log.isDebugEnabled()) log.debug(pstmt.toString());
            pstmt.executeUpdate();
            pstmt.close();

        } catch (SQLException e) {
            throw ReposException.create(log, e);

        }
    }
    
    public static void deleteTable(Class<? extends AbstractReposTable> tableClass, String whereClause) throws ReposException {
        try {
            // generating delete query
            String className = tableClass.getSimpleName();
            Query query = new Query("DELETE FROM " + prefix + className);
            if (whereClause != null) {
                query.append(" WHERE " + whereClause);
            }
            query.append(";");
            // generating prepared statement 
            PreparedStatement pstmt = conn.prepareStatement(query.toString());
            if (log.isDebugEnabled()) log.debug(pstmt.toString());
            pstmt.executeUpdate();
            pstmt.close();

        } catch (SQLException e) {
            throw ReposException.create(log, e);

        }
    }
}
